﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data;
using System.Reflection;
using System.Data.SqlClient;
using Oracle.DataAccess.Client;
using System.Data.DataModel;


namespace System.Data.DataControl
{
    public class SqlController : DbController
    {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="connectionString"></param>
        public override void SetCommand()
        {
            this.command = new SqlCommand();
            this.command.Connection = DataConnection.Connection;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        protected override DbDataReader GetDataReader()
        {
            return (SqlDataReader)this.command.ExecuteReader();
        }


        public override void SetParameters(object obj, string[] primaryKey)
        {
            Type type = obj.GetType();
            Array.ForEach<PropertyInfo>(type.GetProperties(), delegate(PropertyInfo property)
            {
                string pkPropertyName = String.Empty;
                if(primaryKey != null)
                {
                    foreach (string pkProperty in primaryKey)
                    {
                        if (property.Name.Equals(pkProperty))
                        {
                            return;
                        }
                    }
                }
        
                this.SetParameter(property, obj);
 
            });
        }

        public override void SetParameter(PropertyInfo property, object obj)
        {
            this.command.Parameters.Add(new SqlParameter(property.Name, property.GetValue(obj, null)));
        }

        public override void SetParameter(string parameterName, object parameterValue)
        {
            this.command.Parameters.Add(new SqlParameter(parameterName, parameterValue));
        }

        public override int CreateTable(bool dropTable, string className, List<PropertySet> properties, string filePath)
        {

            StringBuilder builder = new StringBuilder();
            StringBuilder pkBuilder = new StringBuilder();
            if (dropTable)
            {
                builder.AppendLine("IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + className + "]') AND type in (N'U'))");
                builder.AppendLine("DROP TABLE [dbo].[" + className + "]");
            }
            builder.AppendLine("SET ANSI_NULLS ON");
            builder.AppendLine("SET QUOTED_IDENTIFIER ON");
            builder.AppendLine("CREATE TABLE [dbo].[" + className + "](");

            int pkCount = 0;
            properties.ForEach(delegate(PropertySet property)
            {
                builder.Append("\t[" + property.Name + "]");
                builder.Append(
                    property.Type.Name.Equals(typeof(System.Int32).Name) ? "[int]" :
                    property.Type.Name.Equals(typeof(System.Double).Name) ? "[bigint]" :
                    property.Type.Name.Equals(typeof(System.DateTime).Name) ? "[datetime]" :
                    property.Type.Name.Equals(typeof(System.Boolean).Name) ? "[tinyint]" :
                    property.Type.Name.Equals(typeof(System.Single).Name) ? "[float]" :
                    "[nvarchar]" + "(" + property.Size.ToString() + ")"
                    );

                if (property.PrimaryKey)
                {
                    builder.Append(" NOT NULL");
                    pkBuilder.Append("\t\t[" + property.Name + "] ASC,");
                    pkCount++;
                }
                builder.Append(",\r\n");
            });
            if (pkCount > 0)
            {
                pkBuilder.Remove(pkBuilder.Length - 1, 1);
                builder.AppendLine("\tCONSTRAINT [PK_" + className + "] PRIMARY KEY CLUSTERED ");
                builder.AppendLine("\t(");
                builder.AppendLine(pkBuilder.ToString());
                builder.AppendLine("\t)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]");
                builder.AppendLine(") ON [PRIMARY]");
            }
            this.command.CommandText = builder.ToString();
            return base.CreateTable(dropTable, className, properties, filePath);
        }

        #region Constructor
        public SqlController()
        {
            this.SetCommand();
        }



        #endregion
    }
}
